USE [Example.Repository]
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'type.identity')
	BEGIN
	DROP PROCEDURE [dbo].[type.identity]
	END
GO

CREATE PROCEDURE [dbo].[type.identity]
	 @assembly VARCHAR(260)
	,@type VARCHAR(260)
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM [dbo].[assembly] WHERE [name] = @assembly)
		BEGIN
		INSERT INTO [dbo].[assembly] ([name]) VALUES (@assembly)
		END

	DECLARE @assemblyId INT
	SET @assemblyId = (SELECT [identity] FROM [dbo].[assembly] WHERE [name] = @assembly)

	IF NOT EXISTS (SELECT * FROM [dbo].[type] WHERE [assembly] = @assemblyId AND [name] = @type)
		BEGIN
		INSERT INTO [dbo].[type] ([assembly], [name]) VALUES (@assemblyId, @type)
		END

	DECLARE @identity INT
    SET @identity = (SELECT [identity] FROM [dbo].[type] WHERE [assembly] = @assemblyId AND [name] = @type)
    
    RETURN @identity
END
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.delete.urn')
	BEGIN
	DROP PROCEDURE [dbo].[object.delete.urn]
	END
GO

CREATE PROCEDURE [dbo].[object.delete.urn]
	@urn VARCHAR(260)
AS
DELETE FROM [dbo].[object]
WHERE [urn] = @urn
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.delete.token')
	BEGIN
	DROP PROCEDURE [dbo].[object.delete.token]
	END
GO

CREATE PROCEDURE [dbo].[object.delete.token]
	@token UNIQUEIDENTIFIER
AS
DELETE FROM [dbo].[object] WHERE [token] = @token
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.exists.urn')
	BEGIN
	DROP PROCEDURE [dbo].[object.exists.urn]
	END
GO

CREATE PROCEDURE [dbo].[object.exists.urn]
	@urn VARCHAR(260)
AS
DECLARE @exists BIT
SET @exists = 0

IF EXISTS
(
	SELECT *
	FROM [dbo].[object] WITH (NOLOCK)
	WHERE [urn] = @urn
)
	BEGIN
	SET @exists = 1
	END

SELECT @exists
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.exists.token')
	BEGIN
	DROP PROCEDURE [dbo].[object.exists.token]
	END
GO

CREATE PROCEDURE [dbo].[object.exists.token]
	@token UNIQUEIDENTIFIER
AS
DECLARE @exists BIT
SET @exists = 0

IF EXISTS (SELECT * FROM [dbo].[object] WITH (NOLOCK) WHERE [token] = @token)
	BEGIN
	SET @exists = 1
	END

SELECT @exists
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.exists.xpath')
	BEGIN
	DROP PROCEDURE [dbo].[object.exists.xpath]
	END
GO

CREATE PROCEDURE [dbo].[object.exists.xpath]
	@xpath NVARCHAR(255)
AS
DECLARE @sql nvarchar(255)
SET @sql = N'SELECT COUNT(*) FROM [object] WITH (NOLOCK) WHERE [xml].exist(''' + @xpath + ''') <> 0'
EXEC sp_executesql @sql
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.insert')
	BEGIN
	DROP PROCEDURE [dbo].[object.insert]
	END
GO

CREATE PROCEDURE [dbo].[object.insert]
	 @xml XML
	,@assembly VARCHAR(260)
	,@type VARCHAR(260)
	,@urn VARCHAR(260)
	,@created DATETIME = NULL
	,@modified DATETIME
	,@status INT = 200
	,@cacheability INT = 3
	,@etag CHAR(24)
	,@expires VARCHAR(50) = ''
AS
IF (@created IS NULL)
(
	SELECT @created = @modified
)

DECLARE @typeId INT
EXECUTE @typeId = [dbo].[type.identity] @assembly, @type

DECLARE @temp TABLE ([token] UNIQUEIDENTIFIER)
INSERT INTO [dbo].[object]
(
	 [xml]
	,[type]
	,[urn]
	,[created]
	,[modified]
	,[status]
	,[cacheability]
	,[etag]
	,[expires]
)
OUTPUT INSERTED.[token] INTO @temp
VALUES
(
	 @xml
	,@typeId
	,@urn
	,@created
	,@modified
	,@status
	,@cacheability
	,@etag
	,@expires
)
SELECT [token] FROM @temp
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.match.urn')
	BEGIN
	DROP PROCEDURE [dbo].[object.match.urn]
	END
GO

CREATE PROCEDURE [dbo].[object.match.urn]
	 @urn VARCHAR(260)
	,@etag CHAR(24)
AS
DECLARE @result BIT
SET @result = 0
IF EXISTS
(
	SELECT *
	FROM [dbo].[object] WITH (NOLOCK)
	WHERE [urn] = @urn AND [etag] = @etag
)
	BEGIN
	SET @result = 1
	END
SELECT @result;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.match.token')
	BEGIN
	DROP PROCEDURE [dbo].[object.match.token]
	END
GO

CREATE PROCEDURE [dbo].[object.match.token]
	 @token UNIQUEIDENTIFIER
	,@etag CHAR(24)
AS
DECLARE @result BIT
SET @result = 0
IF EXISTS
(
	SELECT *
	FROM [dbo].[object] WITH (NOLOCK)
	WHERE [token] = @token AND [etag] = @etag
)
	BEGIN
	SET @result = 1
	END
SELECT @result;
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.modified.urn')
	BEGIN
	DROP PROCEDURE [dbo].[object.modified.urn]
	END
GO

CREATE PROCEDURE [dbo].[object.modified.urn]
	@urn VARCHAR(260)
AS
SELECT [modified]
FROM [dbo].[object] WITH (NOLOCK)
WHERE [urn] = @urn
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.modified.token')
	BEGIN
	DROP PROCEDURE [dbo].[object.modified.token]
	END
GO

CREATE PROCEDURE [dbo].[object.modified.token]
	@token UNIQUEIDENTIFIER
AS
SELECT [modified]
FROM [dbo].[object] WITH (NOLOCK)
WHERE [token] = @token
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.select.urn')
	BEGIN
	DROP PROCEDURE [dbo].[object.select.urn]
	END
GO

CREATE PROCEDURE [dbo].[object.select.urn]
	@urn VARCHAR(260)
AS
SELECT
	 o.[xml]
	,o.[token]
	,o.[urn]
	,o.[created]
	,o.[modified]
	,o.[status]
	,o.[cacheability]
	,o.[etag]
	,o.[expires]
	,(t.[name] + ', ' + a.[name]) AS [type]
FROM [dbo].[object] AS o WITH (NOLOCK)
	INNER JOIN [dbo].[type] AS t ON t.[identity] = o.[type]
	INNER JOIN [dbo].[assembly] AS a ON a.[identity] = t.[assembly]
WHERE [urn] = @urn
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.select.token')
	BEGIN
	DROP PROCEDURE [dbo].[object.select.token]
	END
GO

CREATE PROCEDURE [dbo].[object.select.token]
	 @token UNIQUEIDENTIFIER
AS
SELECT
	 o.[xml]
	,o.[token]
	,o.[urn]
	,o.[created]
	,o.[modified]
	,o.[status]
	,o.[cacheability]
	,o.[etag]
	,o.[expires]
	,(t.[name] + ', ' + a.[name]) AS [type]
FROM [dbo].[object] AS o WITH (NOLOCK)
	INNER JOIN [dbo].[type] AS t ON t.[identity] = o.[type]
	INNER JOIN [dbo].[assembly] AS a ON a.[identity] = t.[assembly]
WHERE [token] = @token
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.select.xpath')
	BEGIN
	DROP PROCEDURE [dbo].[object.select.xpath]
	END
GO

CREATE PROCEDURE [dbo].[object.select.xpath]
	@xpath NVARCHAR(255)
AS
DECLARE @sql nvarchar(255)
SET @sql = N'SELECT [xml], [token], [urn], [created], [modified], [status], [cacheability], [etag], [expires] FROM [object] WITH (NOLOCK) WHERE [xml].exist(''' + @xpath + ''') <> 0'
EXEC sp_executesql @sql
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.token')
	BEGIN
	DROP PROCEDURE [dbo].[object.token]
	END
GO

CREATE PROCEDURE [dbo].[object.token]
	 @urn VARCHAR(260)
AS
SELECT [token]
FROM [dbo].[object] WITH (NOLOCK)
WHERE [urn] = @urn
GO

IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = N'object.update')
	BEGIN
	DROP PROCEDURE [dbo].[object.update]
	END
GO

CREATE PROCEDURE [dbo].[object.update]
	 @xml XML
	,@assembly VARCHAR(260)
	,@type VARCHAR(260)
	,@urn VARCHAR(260)
	,@modified DATETIME
	,@status INT = 200
	,@cacheability INT = 3
	,@etag CHAR(24)
	,@expires VARCHAR(50) = ''
	,@token UNIQUEIDENTIFIER
AS
DECLARE @typeId INT
EXECUTE @typeId = [dbo].[type.identity] @assembly, @type

UPDATE [dbo].[object] SET
	 [xml] = @xml
	,[type] = @typeId
	,[urn] = @urn
	,[modified] = @modified
	,[status] = @status
	,[cacheability] = @cacheability
	,[etag] = @etag
	,[expires] = @expires
WHERE [token] = @token
GO